#' counting POI data from cityRE database.
#'
#' This function count POI data from cityRE database.
#' @param con .MySQL connection object
#' @export
#' @return a dataframe object contains all poi count and outofChina count
#' @examples
#' con<-connectDB()
#' count_POI(con=con)
count_POI<-function(con=NULL,verbose=F)
{
  needclose<-F
  library(dplyr)
  if(is.null(con)){
    warning('trying connect database with default parameters because con is null!')
    con<-connectDB()
    needclose<-T
  }
  group.by<-'group by city_code'
  sql_cmd<-paste('select city_code,count(ha_code) as allpoi_count from ha_poi',
                 group.by)
  if(verbose){cat(sql_cmd,'\n')}
  allpoi_count<-dbGetQuery(con,sql_cmd)
  sql_poi_count<-paste(
    'select ha_poi.city_code,count(ha_poi.ha_code) as poi_count',
    ' from ha_poi')
  edu.poi.con<-paste(' where ha_cl_name=\"教育" and ',
                     '(ha_cl_name_child=\"中学\" or ',
                     ' ha_cl_name_child=\"小学\" or  ',
                     'ha_cl_name_child=\"幼儿园\" )')
  sql_cmd<-paste(sql_poi_count,edu.poi.con,group.by)
  if(verbose){cat(sql_cmd,'\n')}
  edu.poi.count<-dbGetQuery(con,sql_cmd)
  names(edu.poi.count)[2]<-'edu.poi.count'
  hosi.poi.con<-paste(' where ha_cl_name_child=\"医院" ')
  sql_cmd<-paste(sql_poi_count,hosi.poi.con,group.by)
  if(verbose){cat(sql_cmd,'\n')}
  hosi.poi.count<-dbGetQuery(con,sql_cmd)
  names(hosi.poi.count)[2]<-'hosi.poi.count'
  busi.poi.con<-paste('where ha_cl_name_child=\"商圈\" or ',
                      ' ha_cl_name_child=\"商业街\" or  ',
                      'ha_cl_name_child=\"商场\" or',
                      'ha_cl_name_child=\"市场\" ')
  sql_cmd<-paste(sql_poi_count,busi.poi.con,group.by)
  if(verbose){cat(sql_cmd,'\n')}
  busi.poi.count<-dbGetQuery(con,sql_cmd)
  names(busi.poi.count)[2]<-'busi.poi.count'
  sql_routes_count<-paste(
    'select city_code,count(station_code) as station_count',
    ' from routes',group.by)
  if(verbose){cat(sql_routes_count,'\n')}
  routes.poi.count<-dbGetQuery(con,sql_routes_count)
  names(routes.poi.count)[2]<-'routes.poi.count'
  sql.sqlofchina<-'x <72.004 or x >137.8347 or y <0.8293 or y > 55.8271'
  sql.ha.gps.out<-paste('select city_code,count(1) as ha_out from ha_gps where',
                        sql.sqlofchina,group.by)
  if(verbose){cat(sql.ha.gps.out,'\n')}
  ha_gps.out.count<-dbGetQuery(con,sql.ha.gps.out)
  sql.poi.out<-paste('select city_code,count(1) as poi_out from ha_poi_gps where',
                     sql.sqlofchina,group.by)
  if(verbose){cat(sql.ha.gps.out,'\n')}
  poi.out.count<-dbGetQuery(con,sql.poi.out)
  sql.find.out<-paste('select * from ha_gps where',sql.sqlofchina,
                  'union select * from ha_poi_gps where',sql.sqlofchina)
  if(verbose){cat(sql.find.out,'\n')}
  data.out<-dbGetQuery(con,sql.find.out)
  if(needclose) dbDisconnect(con)
  return(list(allpoi_count%>%left_join(edu.poi.count)%>%
                left_join(hosi.poi.count)%>%
                left_join(busi.poi.count)%>%
                left_join(routes.poi.count)%>%
                left_join(ha_gps.out.count)%>%
                left_join(poi.out.count),data.out ))
}
